package com.shangyueshang.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shangyueshang.model.GoodsAdvertiseFee;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * @author: JokeyZheng
 * @email: zhengjingfeng@ruqimobility.com
 * @created: 2023/5/17 15:01
 * @version: v1.0.0
 */
@Component
public interface GoodsAdvertiseFeeMapper extends BaseMapper<GoodsAdvertiseFee> {
	
	/**
	 * 查询商品的广告费用
	 *
	 * @param params
	 * @return
	 */
	@Select("<script>" +
			"   SELECT " +
			"       shop," +
			"       sku, " +
			"       asin, " +
			"       sum(impressions) as impressions, " +
			"       sum(hits) as hits, " +
			"       sum(cost * exchange_rate) as cost, " +
			"       DATE_FORMAT(to_date, '%Y-%m') as trade_month" +
			"   FROM " +
			"        goods_advertise_fee " +
			"   <where>" +
			"       <if test='params.shop != null'> " +
			"		 AND shop IN " +
			"			<foreach collection='params.shop' item='item' open='(' separator=',' close=')'>" +
			"				#{item} " +
			"			</foreach>" +
			"       </if> " +
			"       <if test='params.sku != null'> " +
			"           AND sku = #{params.sku} " +
			"       </if> " +
			"       <if test='params.asin != null'> " +
			"           AND asin = #{params.asin} " +
			"       </if> " +
			"       <if test='params.trade_month != null'> " +
			"           AND DATE_FORMAT(to_date, '%Y-%m') = #{params.trade_month} " +
			"       </if> " +
			"   </where>" +
			"   GROUP BY shop, sku " +
			"   ORDER BY trade_month desc, cost desc " +
			"</script>")
	List<GoodsAdvertiseFee> queryAdvertiseFee(@Param("params") Map<String, Object> params);
	
	/**
	 * 查询广告费
	 *
	 * @param params
	 * @return
	 */
	@Select("<script>" +
			"   SELECT " +
			"       * " +
			"   FROM " +
			"        goods_advertise_fee " +
			"   <where>" +
			"       <if test='params.shop != null'> " +
			"		 AND shop IN " +
			"			<foreach collection='params.shop' item='item' open='(' separator=',' close=')'>" +
			"				#{item} " +
			"			</foreach>" +
			"       </if> " +
			"       <if test='params.sku != null'> " +
			"           AND sku = #{params.sku} " +
			"       </if> " +
			"       <if test='params.asin != null'> " +
			"           AND asin = #{params.asin} " +
			"       </if> " +
			"       <if test='params.trade_month != null'> " +
			"           AND DATE_FORMAT(to_date, '%Y-%m') = #{params.trade_month} " +
			"       </if> " +
			"   </where>" +
			"</script>")
	List<GoodsAdvertiseFee> queryAdvertiseFeeList(@Param("params") Map<String, Object> params);
}

